import 'dart:convert';
import 'dart:io';
import 'package:flutter/material.dart';
import 'package:flutter_local_notifications/flutter_local_notifications.dart';
import 'package:intl/intl.dart';
import 'package:sqflite/sqflite.dart';
import 'package:path/path.dart';
import 'package:timezone/timezone.dart' as tz;
import 'package:uuid/uuid.dart';
import '../model/reminder.dart';
import '../model/baby.dart';
import '../model/health_record.dart';
import 'notification_service.dart';

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;

  factory DatabaseService() => _instance;

  DatabaseService._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  Future<Database> _initDatabase() async {
    String path = join(await getDatabasesPath(), 'baby_health.db');
    return await openDatabase(
      path,
      version: 3,
      onCreate: _onCreate,
      onUpgrade: _onUpgrade,
    );
  }

  static Future<void> _onCreate(Database db, int version) async {
    await _createTables(db);
  }

  static Future<void> _createTables(Database db) async {
    await db.execute('PRAGMA foreign_keys = ON');

    // Create babies table
    await db.execute('''
      CREATE TABLE IF NOT EXISTS babies (
        id TEXT PRIMARY KEY,
        name TEXT NOT NULL,
        gender TEXT NOT NULL,
        birthDate TEXT NOT NULL,
        avatarUrl TEXT,
        isDefault INTEGER DEFAULT 0
      )
    ''');

    // Create health_records table
    await db.execute('''
      CREATE TABLE IF NOT EXISTS health_records (
        id TEXT PRIMARY KEY,
        baby_id TEXT NOT NULL,
        type TEXT NOT NULL,
        timestamp TEXT NOT NULL,
        data TEXT NOT NULL,
        FOREIGN KEY (baby_id) REFERENCES babies(id) ON DELETE CASCADE
      )
    ''');

    // Create reminders table
    await db.execute('''
      CREATE TABLE IF NOT EXISTS reminders (
        id TEXT PRIMARY KEY,
        baby_id TEXT NOT NULL,
        title TEXT NOT NULL,
        type TEXT NOT NULL,
        schedule_time TEXT NOT NULL,
        repeat INTEGER DEFAULT 0,
        notes TEXT,
        is_active INTEGER DEFAULT 1,
        FOREIGN KEY (baby_id) REFERENCES babies(id) ON DELETE CASCADE
      )
    ''');
  }

  static Future<void> _onUpgrade(
    Database db,
    int oldVersion,
    int newVersion,
  ) async {
    if (oldVersion < 2) {
      // 添加 isDefault 列
      await db.execute(
        'ALTER TABLE babies ADD COLUMN isDefault INTEGER DEFAULT 0',
      );
    }
    if (oldVersion < 3) {
      await db.execute('''
      CREATE TABLE IF NOT EXISTS reminders (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        baby_id TEXT NOT NULL,
        type TEXT NOT NULL,
        schedule_time TEXT NOT NULL,
        repeat INTEGER DEFAULT 0,
        notes TEXT,
        is_active INTEGER DEFAULT 1,
        FOREIGN KEY (baby_id) REFERENCES babies(id) ON DELETE CASCADE
      )
    ''');
    }
  }

  // 宝宝相关操作
  Future<void> addBaby(Baby baby) async {
    final db = await database;
    await db.insert('babies', {
      'id': Uuid().v4(),
      'name': baby.name,
      'birthDate': baby.birthDate.toIso8601String(),
      'gender': baby.gender,
      'avatarUrl': baby.avatarUrl,
      'isDefault': baby.isDefault ? 1 : 0,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  Future<List<Baby>> getAllBabies() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query('babies');

    return maps
        .map(
          (map) => Baby(
            id: map['id'],
            name: map['name'],
            birthDate: DateTime.parse(map['birthDate']),
            gender: map['gender'],
            avatarUrl: map['avatarUrl'],
            isDefault: (map['isDefault'] as int) == 1,
          ),
        )
        .toList();
  }

  Future<Baby?> getDefaultBaby() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'babies',
      where: 'isDefault = ?',
      whereArgs: [1],
      limit: 1,
    );

    if (maps.isEmpty) {
      // 如果没有默认宝宝，获取第一个宝宝
      final allBabies = await db.query('babies', limit: 1);
      if (allBabies.isEmpty) {
        return null;
      }
      return Baby.fromMap(allBabies.first);
    }

    return Baby.fromMap(maps.first);
  }

  Future<void> setDefaultBaby(String babyId) async {
    final db = await database;
    await db.transaction((txn) async {
      // First, remove default status from all babies
      await txn.update('babies', {'isDefault': 0}, where: 'isDefault = 1');

      // Then, set the selected baby as default
      await txn.update(
        'babies',
        {'isDefault': 1},
        where: 'id = ?',
        whereArgs: [babyId],
      );
    });
  }

  Future<void> updateBaby(Baby baby) async {
    final db = await database;

    try {
      await db.transaction((txn) async {
        // If this baby is being set as default, clear other defaults first
        if (baby.isDefault) {
          await txn.update('babies', {'isDefault': 0}, where: 'isDefault = 1');
        }

        // Update the baby record
        final count = await txn.update(
          'babies',
          baby.toMap(),
          where: 'id = ?',
          whereArgs: [baby.id],
        );

        if (count == 0) {
          throw DatabaseException('Baby with id ${baby.id} not found');
        }
      });
    } catch (e) {
      throw DatabaseException('Failed to update baby: $e');
    }
  }

  Future<void> deleteBaby(String babyId) async {
    final db = await database;

    try {
      await db.transaction((txn) async {
        // First check if this is the only baby
        final count = Sqflite.firstIntValue(
          await txn.rawQuery('SELECT COUNT(*) FROM babies'),
        );

        if (count == 1) {
          throw DatabaseException('Cannot delete the only baby in the system');
        }

        // Delete all health records for this baby
        await txn.delete(
          'health_records',
          where: 'baby_id = ?',
          whereArgs: [babyId],
        );

        // Delete all reminders for this baby
        await txn.delete(
          'reminders',
          where: 'baby_id = ?',
          whereArgs: [babyId],
        );

        // Delete the baby
        final result = await txn.delete(
          'babies',
          where: 'id = ?',
          whereArgs: [babyId],
        );

        if (result == 0) {
          throw DatabaseException('Baby with id $babyId not found');
        }

        // If the deleted baby was default, set another baby as default
        final wasDefault = await txn.query(
          'babies',
          where: 'id = ? AND isDefault = 1',
          whereArgs: [babyId],
        );

        if (wasDefault.isNotEmpty) {
          final remaining = await txn.query('babies', limit: 1);
          if (remaining.isNotEmpty) {
            await txn.update(
              'babies',
              {'isDefault': 1},
              where: 'id = ?',
              whereArgs: [remaining.first['id']],
            );
          }
        }
      });
    } catch (e) {
      throw DatabaseException('Failed to delete baby: $e');
    }
  }

  // 健康记录相关操作
  Future<void> addHealthRecord(HealthRecord record) async {
    final db = await database;
    try {
      await db.insert('health_records', {
        'baby_id': record.babyId,
        'type': record.type.toString(),
        'timestamp': record.timestamp.toIso8601String(),
        'data': jsonEncode(record.data),
      });
    } catch (e) {
      throw DatabaseException('Failed to add health record: $e');
    }
  }

  Future<List<HealthRecord>> getRecentRecords({
    String? babyId,
    int limit = 10,
  }) async {
    final db = await database;
    String query = '''
      SELECT * FROM health_records
      ${babyId != null ? 'WHERE baby_id = ?' : ''}
      ORDER BY timestamp DESC
      LIMIT ?
    ''';

    final List<Map<String, dynamic>> maps = await db.rawQuery(
      query,
      babyId != null ? [babyId, limit] : [limit],
    );

    return maps
        .map(
          (map) => HealthRecord(
            id: map['id'],
            babyId: map['baby_id'],
            type: _parseRecordType(map['type']),
            timestamp: DateTime.parse(map['timestamp']),
            data: jsonDecode(map['data']),
          ),
        )
        .toList();
  }

  Future<List<HealthRecord>> getRecordsByType(
    RecordType type,
    String babyId, {
    DateTime? startDate,
    DateTime? endDate,
  }) async {
    final db = await database;
    String query = '''
      SELECT * FROM health_records
      WHERE baby_id = ? AND type = ?
      ${startDate != null ? 'AND timestamp >= ?' : ''}
      ${endDate != null ? 'AND timestamp <= ?' : ''}
      ORDER BY timestamp DESC
    ''';

    List<dynamic> arguments = [babyId, type.toString()];
    if (startDate != null) arguments.add(startDate.toIso8601String());
    if (endDate != null) arguments.add(endDate.toIso8601String());

    final List<Map<String, dynamic>> maps = await db.rawQuery(query, arguments);

    return maps
        .map(
          (map) => HealthRecord(
            id: map['id'],
            babyId: map['baby_id'],
            type: _parseRecordType(map['type']),
            timestamp: DateTime.parse(map['timestamp']),
            data: jsonDecode(map['data']),
          ),
        )
        .toList();
  }

  Future<void> deleteRecord(String id) async {
    final db = await database;
    await db.delete('health_records', where: 'id = ?', whereArgs: [id]);
  }

  Future<void> updateRecord(HealthRecord record) async {
    final db = await database;
    await db.update(
      'health_records',
      {
        'type': record.type.toString(),
        'timestamp': record.timestamp.toIso8601String(),
        'data': jsonEncode(record.data),
      },
      where: 'id = ?',
      whereArgs: [record.id],
    );
  }

  Future<List<Reminder>> getTodayReminders(String babyId) async {
    final db = await database;
    final now = DateTime.now();
    final today = DateTime(now.year, now.month, now.day);
    final tomorrow = today.add(Duration(days: 1));

    // 获取今天的提醒和重复提醒
    final List<Map<String, dynamic>> maps = await db.rawQuery(
      '''
      SELECT * FROM reminders 
      WHERE baby_id = ? 
      AND is_active = 1
      AND (
        (schedule_time BETWEEN ? AND ?) 
        OR (
          repeat = 1 
          AND strftime('%H:%M', schedule_time) >= strftime('%H:%M', 'now', 'localtime')
        )
      )
      ORDER BY schedule_time ASC
    ''',
      [babyId, today.toIso8601String(), tomorrow.toIso8601String()],
    );

    return List.generate(maps.length, (i) {
      return Reminder.fromMap(maps[i]);
    });
  }

  // 标记提醒为已完成
  Future<void> markReminderComplete(Reminder reminder) async {
    final db = await database;

    if (reminder.repeat) {
      // 如果是重复提醒，更新下一次提醒时间
      final nextReminder = reminder.getNextReminder();
      if (nextReminder != null) {
        await db.update(
          'reminders',
          {'schedule_time': nextReminder.toIso8601String()},
          where: 'id = ?',
          whereArgs: [reminder.id],
        );
      }
    } else {
      // 如果不是重复提醒，标记为非活动
      await db.update(
        'reminders',
        {'is_active': 0},
        where: 'id = ?',
        whereArgs: [reminder.id],
      );
    }
  }

  // 统计相关查询
  Future<Map<String, dynamic>> getDailySummary(String babyId) async {
    final db = await database;
    final Map<String, dynamic> result = {};

    try {
      await db.transaction((txn) async {
        // Get feeding amount sum - only count milk amount, not duration
        final feedingRecords = await txn.query(
          'health_records',
          where:
              'baby_id = ? AND type = ? AND timestamp >= datetime("now", "-1 day")',
          whereArgs: [babyId, RecordType.feeding.toString()],
        );

        double totalAmount = 0;
        for (var record in feedingRecords) {
          final data = jsonDecode(record['data'] as String);
          if (data['amount'] != null) {
            // Handle both int and double values
            final amount = num.tryParse(data['amount'].toString()) ?? 0;
            totalAmount += amount;
          }
        }

        // Convert to integer for display
        result['feedingAmount'] = totalAmount.round();

        // Get diaper count
        final diaperCount =
            Sqflite.firstIntValue(
              await txn.rawQuery(
                '''
          SELECT COUNT(*) 
          FROM health_records
          WHERE baby_id = ? 
            AND type = ? 
            AND timestamp >= datetime("now", "-1 day")
        ''',
                [babyId, RecordType.diaper.toString()],
              ),
            ) ??
            0;

        result['diaperCount'] = diaperCount;

        // Get latest temperature in last 24 hours
        final tempRecords = await txn.query(
          'health_records',
          where:
              'baby_id = ? AND type = ? AND timestamp >= datetime("now", "-1 day")',
          whereArgs: [babyId, RecordType.temperature.toString()],
          orderBy: 'timestamp DESC',
          limit: 1,
        );

        if (tempRecords.isNotEmpty) {
          final data = jsonDecode(tempRecords.first['data'] as String);
          final temp = double.tryParse(data['temperature'].toString());
          result['lastTemperature'] = temp?.toStringAsFixed(1);
        } else {
          result['lastTemperature'] = null;
        }
      });

      return result;
    } catch (e) {
      debugPrint('Error fetching daily summary: $e');
      return {'feedingAmount': 0, 'diaperCount': 0, 'lastTemperature': null};
    }
  }

  // 辅助方法
  RecordType _parseRecordType(String type) {
    return RecordType.values.firstWhere(
      (e) => e.toString() == type,
      orElse: () => RecordType.custom,
    );
  }

  Future<List<HealthRecord>> getHealthRecords({
    RecordType? type,
    DateTime? startDate,
    DateTime? endDate,
  }) async {
    final db = await database;
    try {
      List<String> conditions = [];
      List<dynamic> arguments = [];

      if (type != null) {
        conditions.add('type = ?');
        arguments.add(type.toString());
      }

      if (startDate != null) {
        conditions.add('date(timestamp) >= date(?)');
        arguments.add(startDate.toIso8601String());
      }

      if (endDate != null) {
        conditions.add('date(timestamp) <= date(?)');
        arguments.add(endDate.toIso8601String());
      }

      final String whereClause =
          conditions.isEmpty ? '' : 'WHERE ${conditions.join(' AND ')}';
      final String query = '''
        SELECT 
          id,
          baby_id,
          type,
          timestamp,
          data
        FROM health_records
        $whereClause
        ORDER BY timestamp DESC
      ''';

      final List<Map<String, dynamic>> maps = await db.rawQuery(
        query,
        arguments,
      );

      return maps.map((map) {
        return HealthRecord(
          id: map['id'].toString(), // Convert int to String explicitly
          babyId: map['baby_id'] as String,
          type: _parseRecordType(map['type'] as String),
          timestamp: DateTime.parse(map['timestamp'] as String),
          data: jsonDecode(map['data'] as String),
        );
      }).toList();
    } catch (e) {
      debugPrint('Error fetching health records: $e');
      return [];
    }
  }

  Future<void> addReminder(Reminder reminder) async {
    final db = await database;
    int? notificationId;
    Baby? baby;

    try {
      // First get baby info outside transaction
      baby = await getBaby(reminder.babyId);
      if (baby == null) {
        throw DatabaseException('Baby not found');
      }

      // Generate notification ID within 32-bit integer limits
      notificationId = (DateTime.now().millisecondsSinceEpoch & 0x7FFFFFFF);

      // Calculate schedule time
      final now = DateTime.now();
      final scheduledDateTime = DateTime(
        now.year,
        now.month,
        now.day,
        reminder.scheduleTime.hour,
        reminder.scheduleTime.minute,
      );

      final scheduleTime =
          scheduledDateTime.isBefore(now)
              ? scheduledDateTime.add(const Duration(days: 1))
              : scheduledDateTime;

      // Database operation in transaction
      await db.transaction((txn) async {
        await txn.insert('reminders', {
          ...reminder.toMap(),
          'id': notificationId.toString(),
        });
      });

      // Schedule notification after transaction completes
      await NotificationService.instance.scheduleHealthReminder(
        id: notificationId,
        babyName: baby.name,
        type: reminder.type.label,
        scheduledTime: scheduleTime,
        notes: reminder.notes,
        repeats: reminder.repeat,
      );
    } catch (e) {
      // If notification was scheduled but db operation failed, clean up
      if (notificationId != null) {
        await NotificationService.instance.cancelReminder(notificationId);
      }
      throw DatabaseException('Failed to add reminder: $e');
    }
  }

  Future<void> updateReminder(Reminder reminder) async {
    final db = await database;
    Baby? baby;

    try {
      // First get baby info and cancel existing notification outside transaction
      baby = await getBaby(reminder.babyId);
      if (baby == null) {
        throw DatabaseException('Baby not found');
      }

      final notificationId = int.parse(reminder.id!);
      await NotificationService.instance.cancelReminder(notificationId);

      // Calculate schedule time
      final now = DateTime.now();
      final scheduledDateTime = DateTime(
        now.year,
        now.month,
        now.day,
        reminder.scheduleTime.hour,
        reminder.scheduleTime.minute,
      );

      final scheduleTime =
          scheduledDateTime.isBefore(now)
              ? scheduledDateTime.add(const Duration(days: 1))
              : scheduledDateTime;

      // Database operation in transaction
      await db.transaction((txn) async {
        await txn.update(
          'reminders',
          reminder.toMap(),
          where: 'id = ?',
          whereArgs: [reminder.id],
        );
      });

      // Schedule new notification after transaction completes
      await NotificationService.instance.scheduleHealthReminder(
        id: notificationId,
        babyName: baby.name,
        type: reminder.type.label,
        scheduledTime: scheduleTime,
        notes: reminder.notes,
        repeats: reminder.repeat,
      );
    } catch (e) {
      throw DatabaseException('Failed to update reminder: $e');
    }
  }

  Future<void> deleteReminder(String id) async {
    final db = await database;
    try {
      await db.transaction((txn) async {
        await txn.delete('reminders', where: 'id = ?', whereArgs: [id]);

        // Convert string ID to int for canceling notification
        final notificationId = int.parse(id);
        await NotificationService.instance.cancelReminder(notificationId);
      });
    } catch (e) {
      throw DatabaseException('Failed to delete reminder: $e');
    }
  }

  Future<void> clearAllReminders() async {
    final db = await database;

    try {
      await db.transaction((txn) async {
        // Delete all reminders from database
        await txn.delete('reminders');

        // Cancel all notifications
        await NotificationService.instance.cancelAllReminders();
      });
    } catch (e) {
      throw DatabaseException('Failed to clear reminders: $e');
    }
  }

  Future<void> _scheduleNotification({
    required int id,
    required String title,
    required String body,
    required DateTime scheduleTime,
    bool repeat = false,
    String? repeatPattern,
  }) async {
    final flutterLocalNotificationsPlugin = FlutterLocalNotificationsPlugin();

    // Create notification details
    const androidPlatformChannelSpecifics = AndroidNotificationDetails(
      'baby_health_tracker_reminders',
      'Reminders',
      channelDescription: 'Notifications for baby health reminders',
      importance: Importance.max,
      priority: Priority.high,
      showWhen: true,
    );

    final platformChannelSpecifics = NotificationDetails(
      android: androidPlatformChannelSpecifics,
    );

    // Schedule the notification
    if (repeat && repeatPattern != null) {
      // Handle repeating notifications
      switch (repeatPattern) {
        case 'daily':
          await flutterLocalNotificationsPlugin.zonedSchedule(
            id,
            title,
            body,
            _nextInstanceOfTime(TimeOfDay.fromDateTime(scheduleTime)),
            platformChannelSpecifics,

            matchDateTimeComponents: DateTimeComponents.time,
            androidScheduleMode: AndroidScheduleMode.exactAllowWhileIdle,
          );
          break;
        case 'weekly':
          await flutterLocalNotificationsPlugin.zonedSchedule(
            id,
            title,
            body,
            _nextInstanceOfDayAndTime(scheduleTime),
            platformChannelSpecifics,
            matchDateTimeComponents: DateTimeComponents.dayOfWeekAndTime,
            androidScheduleMode: AndroidScheduleMode.exactAllowWhileIdle,
          );
          break;
        default:
          // Custom repeat patterns can be handled here
          break;
      }
    } else {
      // One-time notification
      await flutterLocalNotificationsPlugin.zonedSchedule(
        id,
        title,
        body,
        tz.TZDateTime.from(scheduleTime, tz.local),
        platformChannelSpecifics,
        androidScheduleMode: AndroidScheduleMode.exactAllowWhileIdle,
      );
    }
  }

  tz.TZDateTime _nextInstanceOfTime(TimeOfDay time) {
    final now = tz.TZDateTime.now(tz.local);
    var scheduledDate = tz.TZDateTime(
      tz.local,
      now.year,
      now.month,
      now.day,
      time.hour,
      time.minute,
    );

    if (scheduledDate.isBefore(now)) {
      scheduledDate = scheduledDate.add(const Duration(days: 1));
    }
    return scheduledDate;
  }

  tz.TZDateTime _nextInstanceOfDayAndTime(DateTime dateTime) {
    tz.TZDateTime scheduledDate = tz.TZDateTime(
      tz.local,
      dateTime.year,
      dateTime.month,
      dateTime.day,
      dateTime.hour,
      dateTime.minute,
    );

    while (scheduledDate.isBefore(tz.TZDateTime.now(tz.local))) {
      scheduledDate = scheduledDate.add(const Duration(days: 7));
    }
    return scheduledDate;
  }

  Future<Map<int, int>> getFeedingDistribution({
    required String babyId,
    required DateTimeRange dateRange,
  }) async {
    final db = await database;
    final records = await db.query(
      'health_records',
      where: 'baby_id = ? AND type = ? AND timestamp BETWEEN ? AND ?',
      whereArgs: [
        babyId,
        RecordType.feeding.toString(),
        dateRange.start.toIso8601String(),
        dateRange.end.toIso8601String(),
      ],
    );

    Map<int, int> hourlyDistribution = {};
    for (int i = 0; i < 24; i++) {
      hourlyDistribution[i] = 0;
    }

    for (var record in records) {
      final timestamp = DateTime.parse(record['timestamp'] as String);
      hourlyDistribution[timestamp.hour] =
          (hourlyDistribution[timestamp.hour] ?? 0) + 1;
    }

    return hourlyDistribution;
  }

  Future<Map<String, dynamic>> getFeedingSummary({
    required String babyId,
    required DateTimeRange dateRange,
  }) async {
    final db = await database;
    final records = await db.query(
      'health_records',
      where: 'baby_id = ? AND type = ? AND timestamp BETWEEN ? AND ?',
      whereArgs: [
        babyId,
        RecordType.feeding.toString(),
        dateRange.start.toIso8601String(),
        dateRange.end.toIso8601String(),
      ],
      orderBy: 'timestamp ASC',
    );

    if (records.isEmpty) {
      return {
        'totalCount': 0,
        'totalAmount': 0,
        'averageInterval': 0,
        'averageDaily': 0,
        'daytimeCount': 0,
        'nightCount': 0,
        'peakHour': 0,
        'averageDuration': 0,
      };
    }

    int totalAmount = 0;
    int daytimeCount = 0;
    int nightCount = 0;
    Map<int, int> hourCount = {};

    for (var record in records) {
      final data = jsonDecode(record['data'] as String);
      final timestamp = DateTime.parse(record['timestamp'] as String);

      totalAmount += (data['amount'] as num).toInt();

      final hour = timestamp.hour;
      if (hour >= 6 && hour < 18) {
        daytimeCount++;
      } else {
        nightCount++;
      }

      hourCount[hour] = (hourCount[hour] ?? 0) + 1;
    }

    final days = dateRange.duration.inDays + 1;
    final peakHour =
        hourCount.entries.reduce((a, b) => a.value > b.value ? a : b).key;

    Duration totalInterval = Duration.zero;
    for (int i = 1; i < records.length; i++) {
      final current = DateTime.parse(records[i]['timestamp'] as String);
      final previous = DateTime.parse(records[i - 1]['timestamp'] as String);
      totalInterval += current.difference(previous);
    }

    return {
      'totalCount': records.length,
      'totalAmount': totalAmount,
      'averageInterval': (totalInterval.inMinutes / (records.length - 1) / 60)
          .toStringAsFixed(1),
      'averageDaily': (records.length / days).toStringAsFixed(1),
      'daytimeCount': daytimeCount,
      'nightCount': nightCount,
      'peakHour': peakHour,
      'averageDuration':
          0, // Add duration calculation if available in your data
    };
  }

  Future<void> clearAllData() async {
    final db = await database;

    try {
      await db.transaction((txn) async {
        await txn.delete('health_records');
        await txn.delete('reminders');
        await txn.delete('babies');

        // Reset auto-increment counters if any
        // await txn.execute('DELETE FROM sqlite_sequence');

        // Add a default baby to prevent app crashes
        await txn.insert('babies', {
          'id': DateTime.now().toString(),
          'name': '宝宝',
          'gender': 'M',
          'birthDate': DateTime.now().toIso8601String(),
          'isDefault': 1,
        });
      });
    } catch (e) {
      throw DatabaseException('Failed to clear database: $e');
    }
  }

  Future<void> exportData(File file) async {
    final db = await database;

    try {
      // Get all data from each table
      final babies = await db.query('babies');
      final healthRecords = await db.query('health_records');
      final reminders = await db.query('reminders');

      // Create export data structure
      final Map<String, dynamic> exportData = {
        'version': 1,
        'timestamp': DateTime.now().toIso8601String(),
        'babies': babies,
        'health_records': healthRecords,
        'reminders': reminders,
      };

      // Convert to JSON and write to file
      final jsonString = jsonEncode(exportData);
      await file.writeAsString(jsonString);
    } catch (e) {
      throw DatabaseException('Failed to export data: $e');
    }
  }

  // Add corresponding import method for data restoration
  Future<void> importData(File file) async {
    try {
      final jsonString = await file.readAsString();
      final importData = jsonDecode(jsonString) as Map<String, dynamic>;

      // Version check
      if (importData['version'] != 1) {
        throw DatabaseException('Unsupported backup version');
      }

      final db = await database;
      await db.transaction((txn) async {
        // Clear existing data
        await txn.delete('health_records');
        await txn.delete('reminders');
        await txn.delete('babies');

        // Import babies first
        for (final baby in importData['babies'] as List) {
          await txn.insert('babies', Map<String, dynamic>.from(baby));
        }

        // Import health records
        for (final record in importData['health_records'] as List) {
          await txn.insert('health_records', Map<String, dynamic>.from(record));
        }

        // Import reminders
        for (final reminder in importData['reminders'] as List) {
          await txn.insert('reminders', Map<String, dynamic>.from(reminder));
        }
      });
    } catch (e) {
      throw DatabaseException('Failed to import data: $e');
    }
  }

  Future<Baby?> getBaby(String babyId) async {
    final db = await database;

    try {
      return await db.transaction((txn) async {
        final List<Map<String, dynamic>> maps = await txn.query(
          'babies',
          where: 'id = ?',
          whereArgs: [babyId],
        );

        if (maps.isEmpty) {
          return null;
        }

        return Baby.fromMap(maps.first);
      });
    } catch (e) {
      throw DatabaseException('Failed to get baby: $e');
    }
  }

  Future<void> cancelReminder(int id) async {
    final db = await database;

    try {
      await db.transaction((txn) async {
        // Update reminder status in database
        await txn.update(
          'reminders',
          {'is_active': 0},
          where: 'id = ?',
          whereArgs: [id],
        );

        // Cancel the notification
        await NotificationService.instance.cancelReminder(id);
      });
    } catch (e) {
      throw DatabaseException('Failed to cancel reminder: $e');
    }
  }

  Future<void> cancelAllReminders() async {
    final db = await database;

    try {
      await db.transaction((txn) async {
        // Update all reminders status in database
        await txn.update('reminders', {'is_active': 0});

        // Cancel all notifications
        await NotificationService.instance.cancelAllReminders();
      });
    } catch (e) {
      throw DatabaseException('Failed to cancel all reminders: $e');
    }
  }

  // Helper method to ensure database consistency
  Future<void> syncReminders() async {
    final db = await database;

    try {
      final List<Map<String, dynamic>> activeReminders = await db.query(
        'reminders',
        where: 'is_active = 1',
      );

      for (final reminder in activeReminders) {
        final baby = await getBaby(reminder['baby_id'] as String);
        if (baby != null) {
          await NotificationService.instance.scheduleHealthReminder(
            id: reminder['id'] as int,
            babyName: baby.name,
            type: reminder['type'] as String,
            scheduledTime: DateTime.parse(reminder['schedule_time'] as String),
            notes: reminder['notes'] as String?,
            repeats: reminder['repeat'] == 1,
            repeatInterval:
                reminder['repeat'] == 1 ? RepeatInterval.daily : null,
          );
        }
      }
    } catch (e) {
      throw DatabaseException('Failed to sync reminders: $e');
    }
  }

  Future<List<DailyFeedingSummary>> getDailyFeedingSummary(String babyId) async {
    final db = await database;
    try {
      // First get dates with feeding records
      final List<Map<String, dynamic>> dates = await db.rawQuery('''
      SELECT DISTINCT date(timestamp) as day
      FROM health_records
      WHERE baby_id = ? AND type = ?
      AND date(timestamp) >= date('now', '-7 days')
      ORDER BY day ASC
    ''', [babyId, RecordType.feeding.toString()]);

      List<DailyFeedingSummary> summaries = [];

      // Then process each day separately
      for (var dateRow in dates) {
        final day = dateRow['day'] as String;

        final records = await db.query(
          'health_records',
          where: 'baby_id = ? AND type = ? AND date(timestamp) = ?',
          whereArgs: [babyId, RecordType.feeding.toString(), day],
        );

        int count = records.length;
        double totalAmount = 0;

        for (var record in records) {
          final data = jsonDecode(record['data'] as String);
          if (data['amount'] != null) {
            final amount = num.tryParse(data['amount'].toString()) ?? 0;
            totalAmount += amount;
          }
        }

        summaries.add(DailyFeedingSummary(
          date: DateTime.parse(day),
          count: count,
          totalAmount: totalAmount.round(),
        ));
      }

      return summaries;
    } catch (e) {
      debugPrint('Error fetching daily feeding summary: $e');
      return [];
    }
  }

  Future<Map<String, DateTime?>> getLastEventTimes(String babyId) async {
    final db = await database;
    final Map<String, DateTime?> result = {};

    try {
      for (final type in ['feeding', 'diaper', 'temperature']) {
        final List<Map<String, dynamic>> records = await db.query(
          'health_records',
          where: 'baby_id = ? AND type = ?',
          whereArgs: [babyId, type],
          orderBy: 'timestamp DESC',
          limit: 1,
        );

        if (records.isNotEmpty) {
          result[type] = DateTime.parse(records.first['timestamp'] as String);
        } else {
          result[type] = null;
        }
      }
      return result;
    } catch (e) {
      debugPrint('Error fetching last event times: $e');
      return {};
    }
  }

  Future<List<HealthRecord>> getRecentRecordsByType({
    required String babyId,
    required RecordType type,
    int limit = 3,
  }) async {
    final db = await database;
    try {
      final List<Map<String, dynamic>> maps = await db.query(
        'health_records',
        where: 'baby_id = ? AND type = ?',
        whereArgs: [babyId, type.toString()],
        orderBy: 'timestamp DESC',
        limit: limit,
      );

      return maps
          .map(
            (map) => HealthRecord(
              id: map['id'].toString(),
              babyId: map['baby_id'] as String,
              type: _parseRecordType(map['type'] as String),
              timestamp: DateTime.parse(map['timestamp'] as String),
              data: jsonDecode(map['data'] as String),
            ),
          )
          .toList();
    } catch (e) {
      debugPrint('Error fetching recent records by type: $e');
      return [];
    }
  }

  Future<List<Reminder>> getReminders(String babyId) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'reminders',
      where: 'baby_id = ?',
      whereArgs: [babyId],
      orderBy: 'schedule_time ASC',
    );

    return maps.map((map) => Reminder.fromMap(map)).toList();
  }
}

class DailySummary {
  final int count;
  final int totalAmount;

  DailySummary({required this.count, required this.totalAmount});
}

// Data class for daily feeding summary
class DailyFeedingSummary {
  final DateTime date;
  final int count;
  final int totalAmount;

  const DailyFeedingSummary({
    required this.date,
    required this.count,
    required this.totalAmount,
  });

  double get averageAmount => count > 0 ? totalAmount / count : 0;
}

// Extension method to format feeding data
extension FeedingDataFormatter on HealthRecord {
  String get formattedAmount {
    if (type != RecordType.feeding) return '';
    num amount;
    if(data['feedingAmount']!=null){
      amount = num.tryParse(data['feedingAmount']) as num;
    }
    else{
      amount= num.tryParse(data['amount'].toString()) as num;
    }
    return '${amount.toStringAsFixed(0)}ml';
  }

  String get formattedTime {
    return DateFormat('HH:mm').format(timestamp);
  }

  String get formattedDate {
    return DateFormat('yyyy-MM-dd').format(timestamp);
  }
}

class DatabaseException implements Exception {
  final String message;
  DatabaseException(this.message);

  @override
  String toString() => message;
}
